Little Xquery With SqlServer2005:-Part2

Posted: March 1, 2006 in Uncategorized

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

Below is the code where I am going to show is the Xquery way of retrieve from the xml insert into the table and finally we ll get the same xml back from the table all without using the XML explicit mode and openxml and sp_xml_preparedocument .

 

Parent Child XMl With Attribute Centric way:-

declare @inParameterXML xml

set @inParameterXML=’ <RootXml>

 <Parent

 RowID=”1″ PCodeId = “31953” CardNo = “1” Action=”R”  ErrorMessage=”what a error”  STPUPNo=”247″>

<Child RowID=”1″ ChildCodeId=”39″ CardNo=”1″ versionCodeid=”181″ />

<Child RowID=”1″ ChildCodeId=”40″ CardNo=”1″ versionCodeid=”182″ />

<Child RowID=”1″ ChildCodeId=”41″ CardNo=”1″ versionCodeid=”190″ />

<Child RowID=”1″ ChildCodeId=”42″ CardNo=”1″ versionCodeid=”201″ />

<Child RowID=”1″ ChildCodeId=”43″ CardNo=”1″ versionCodeid=”203″ />

</Parent>

<Parent

 RowID=”2″ PCodeId = “31953” CardNo = “1” Action=”R”  ErrorMessage=”what a error”  STPUPNo=”247″>

<Child RowID=”2″ ChildCodeId=”39″ CardNo=”1″ versionCodeid=”181″ />

<Child RowID=”2″ ChildCodeId=”40″ CardNo=”1″ versionCodeid=”182″ />

<Child RowID=”2″ ChildCodeId=”41″ CardNo=”1″ versionCodeid=”190″ />

<Child RowID=”2″ ChildCodeId=”42″ CardNo=”1″ versionCodeid=”201″ />

<Child RowID=”2″ ChildCodeId=”43″ CardNo=”1″ versionCodeid=”203″ />

</Parent>

</RootXml>’

DECLARE  @Parents  TABLE   ( RowID Int ,Action Char(1) , PCodeId  Int , CardNo   Int , ErrorMessage   Varchar(50) ) 

 DECLARE @Childs  TABLE   ( ManageCOId   intIDENTITY(1,1) , RowID  Int  , ChildCodeId   Int , CardNo Int , versionCodeid   Int  )

 

  INSERTINTO @Parents

     SELECT  T.U.value(‘@RowID’,’Int’)                     AS [RowID]    

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

           , T.U.value(‘@PCodeId’,’Int’)                AS [PCodeId]

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

           , T.U.value(‘@ErrorMessage’,’Varchar(500)’)     AS [ErrorMessage]

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

 

INSERTINTO @Childs

     SELECT T.U.value(‘@RowID’,’Int’)                      AS [RowID]

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

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

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

      FROM @inParameterXML.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