XML VALUE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML VALUE

Author

Topic

dyoung1006
Starting Member

2 Posts

Posted - 2013-11-06 : 14:41:57

Im trying to do an exist or value not sure which on an xml field.

this is what i have

xml output:
<auditElement>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
</auditElement>

or
<propagationField>Family</propagationField>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
<field id="1037375" type="8" name="00_APC_Issues" formatstring="" />
<field id="1037379" type="8" name="00_BPC_Issues" formatstring="" />
<field id="1037380" type="8" name="00_CPC_Issues" formatstring="" />
<field id="1037385" type="8" name="00_DPC_Issues" formatstring="" />
<field id="1140067" type="5" name="10_SomeOTHERCODE" formatstring="" />
<field id="1627564" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627565" type="4" name="00_EFPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627566" type="4" name="00_LPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627567" type="4" name="00_PPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2155721" type="8" name="00_EPC_Issues" formatstring="" />
<field id="2155722" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190599" type="8" name="00_CPUBPC_Issues" formatstring="" />
<field id="2190600" type="8" name="00_LPUBPC_Issues" formatstring="" />
<field id="2190601" type="8" name="00_PPUBPC_Issues" formatstring="" />
<field id="2190603" type="4" name="00_CBUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190604" type="4" name="00_LPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2437178" type="4" name="00_PPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="3047764" type="8" name="00_FPUBPC_Issues" formatstring="" />
<field id="3051998" type="4" name="00_FPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
</auditElement>

what i would like to do is pull anything of the actual values of the <fieldID> and <setChoice> values to join them up and only pull back the records I really want.

how can I do this with the .value or.exists references? Any help is appreciated also any good reference material is appreciated.

this is what I have so far

DECLARE @test TABLE (RECID INT, DETAIL XML)
INSERT @test
SELECT ar.ID, ar.Details
FROM EDDSDBO.AuditRecord ar (NOLOCK)
JOIN @DocumentScope ds ON ar.ArtifactID = ds.DocumentArtifactID
JOIN @User u ON u.UserID = ar.UserID
WHERE ar.[Action] IN (3, 4, 5, 6)

SELECT * FROM @test t
--been playing with this
SELECT *, CAST(t.DETAIL AS XML).value('(./field@ID)','INT') FROM @test t

received this error
XQuery [@test.DETAIL.value()]: ")" was expected.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 04:29:05

[code]
declare @x xml ='<auditElement><propagationField>Family</propagationField>
<field id="1037373" type="5" name="10_SomeCODE" formatstring="">
<setChoice>1037392</setChoice>
</field>
<field id="1037375" type="8" name="00_APC_Issues" formatstring="" />
<field id="1037379" type="8" name="00_BPC_Issues" formatstring="" />
<field id="1037380" type="8" name="00_CPC_Issues" formatstring="" />
<field id="1037385" type="8" name="00_DPC_Issues" formatstring="" />
<field id="1140067" type="5" name="10_SomeOTHERCODE" formatstring="" />
<field id="1627564" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627565" type="4" name="00_EFPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627566" type="4" name="00_LPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="1627567" type="4" name="00_PPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2155721" type="8" name="00_EPC_Issues" formatstring="" />
<field id="2155722" type="4" name="00_EPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190599" type="8" name="00_CPUBPC_Issues" formatstring="" />
<field id="2190600" type="8" name="00_LPUBPC_Issues" formatstring="" />
<field id="2190601" type="8" name="00_PPUBPC_Issues" formatstring="" />
<field id="2190603" type="4" name="00_CBUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2190604" type="4" name="00_LPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="2437178" type="4" name="00_PPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
<field id="3047764" type="8" name="00_FPUBPC_Issues" formatstring="" />
<field id="3051998" type="4" name="00_FPUBPC_Comments" formatstring="">
<oldValue />
<newValue />
</field>
</auditElement>'

SELECT m.n.value('(./@id)[1]','int') AS ID,
m.n.value('(./setChoice)[1]','varchar(100)') as SetChoice
FROM @X.nodes('/auditElement/field') m(n)

ID SetChoice
-----------------------
1037373 1037392
1037375 NULL
1037379 NULL
1037380 NULL
1037385 NULL
1140067 NULL
1627564 NULL
1627565 NULL
1627566 NULL
1627567 NULL
2155721 NULL
2155722 NULL
2190599 NULL
2190600 NULL
2190601 NULL
2190603 NULL
2190604 NULL
2437178 NULL
3047764 NULL
3051998 NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 04:30:15

In your case since it comes from table column you need to use like


SELECT m.n.value('(./@id)[1]','int') AS ID,
m.n.value('(./setChoice)[1]','varchar(100)') as SetChoice
FROM @test t
CROSS APPLY DETAIL.nodes('/auditElement/field') m(n)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dyoung1006
Starting Member

2 Posts

Posted - 2013-11-07 : 12:53:19

Thank you,visakh16!

I fixed this yesterday and didn't update my initial post.

I found some very helpful info from here:
http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

this was my final statement
--I ended up join on codes I was looking for to only pull back records I def wanted.
----Distinct list of Audit Record ID's
CREATE TABLE #AuditRecord
(RECID INT PRIMARY KEY)

INSERT INTO #AuditRecord (RECID)
SELECT DISTINCT RECID --DocumentArtifactID, UserName, [TimeStamp], ROW_NUMBER() OVER (PARTITION BY DocumentArtifactID ORDER BY [TIMESTAMP] ASC) AS 'RowNum'
FROM
(
SELECT
RECID,
c.value('(setChoice/text())[1]','int')'SetChoice',
c.value('(UnsetChoice/text())[1]','int') 'UnSetChoice'
FROM @DocumentScope ds CROSS APPLY DETAIL.nodes('/auditElement/field') AS t(c)
) su
JOIN #Codes
ON CodeId = su.SetChoice
OR CodeId = su.UnsetChoice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources