Little Xquery with sqlserver2005:Part3

Posted: March 1, 2006 in Uncategorized

Part1 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!508.entry

Part2 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!509.entry

 

Parent Child RelationShip With Elementric Centric Way:-

declare @inxml xml

set @inxml=’

<RootXml>

 <Parent>

            <Rowid>1</Rowid><PCodeId>31953</PCodeId><CardNo>1</CardNo><Action>R</Action><ErrorMessage>what a error</ErrorMessage>

            <Child><Rowid>1</Rowid><ChildCodeId>39</ChildCodeId><CardNo>1</CardNo><versionCodeid>181</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>40</ChildCodeId><CardNo>1</CardNo><versionCodeid>182</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>41</ChildCodeId><CardNo>1</CardNo><versionCodeid>184</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>42</ChildCodeId><CardNo>1</CardNo><versionCodeid>198</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>43</ChildCodeId><CardNo>1</CardNo><versionCodeid>203</versionCodeid></Child>

</Parent>

 <Parent>

            <Rowid>2</Rowid><PCodeId>31954</PCodeId><CardNo>3</CardNo><Action>R</Action><ErrorMessage>what a error</ErrorMessage>

            <Child><Rowid>2</Rowid><ChildCodeId>29</ChildCodeId><CardNo>5</CardNo><versionCodeid>171</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>30</ChildCodeId><CardNo>5</CardNo><versionCodeid>172</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>31</ChildCodeId><CardNo>5</CardNo><versionCodeid>104</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>32</ChildCodeId><CardNo>5</CardNo><versionCodeid>198</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>33</ChildCodeId><CardNo>5</CardNo><versionCodeid>283</versionCodeid></Child>

</Parent>

 </RootXml>’

 

–USE the Same Table structure

 

  INSERTINTO @Parents

     SELECT  U.value(‘./Rowid[1]’,’Int’)              AS [RowID]    

           , U.value(‘./Action[1]’,’Char(1)’)            AS [Action]

           , U.value(‘./PCodeId[1]’,’Int’)               AS [PCodeId]

           , U.value(‘./CardNo[1]’,’Int’)                AS [CardNo]

           , U.value(‘./ErrorMessage[1]’,’Varchar(50)’)AS [ErrorMessage]

      FROM @inxml.nodes(‘/RootXml/Parent’)AS T(U)

 

INSERTINTO @Childs

     SELECT T.U.value(‘./Rowid[1]’,’Int’)                      AS [RowID]

          , T.U.value(‘./ChildCodeId[1]’,’Int’)                 AS [ChildCodeId]

          , T.U.value(‘./CardNo[1]’,’Int’)                 AS [CardNo]

          , T.U.value(‘./versionCodeid[1]’,’Int’)                      AS [versionCodeid]

      FROM @inxml.nodes(‘/RootXml/Parent/Child’)AS T(U)

 

select      P.[RowID]                                       AS’@RowID’                         

           , P.[Action]                                      AS’@Action’

           , P.[PCodeId]                                  AS’@PCodeId’         

           , P.[CardNo]                                  AS’@CardNo’

           , P.[ErrorMessage]                                AS’@ErrorMessage’

            ,(SELECT

                 C.[RowID]                                       AS’@RowID’                         

                 , C.[ChildCodeId]                                  AS’@ChildCodeId’         

                 , C.[CardNo]                                  AS’@CardNo’

                 , C.[versionCodeid]                                       AS’@versionCodeid’

              FROM  @Childs C 

                                    where P.Rowid=C.RowId

                                    forxmlpath(‘Childs’),Type)

from @Parents P

FORXMLPATH(‘Parent’)  ,ROOT(‘RootXml’)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s