Little Xquery With SqlServer2005:-Part2

Posted: March 1, 2006 in Uncategorized

Part1 :-!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>


 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″ />



 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″ />



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  )



     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)



     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’


                 C.[RowID]                                       AS’@RowID’                          

                 , C.[ChildCodeId]                                  AS’@ChildCodeId’         

                 , C.[CardNo]                                  AS’@CardNo’

                 , C.[versionCodeid]                                       AS’@versionCodeid’

              FROM  @Childs C 

                                    where P.Rowid=C.RowId


from @Parents P

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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s