Query XML in SQL table

[jenlion]

I have XML Data that looks like this.

  <?xml version="1.0" encoding="UTF-8" ?> 
<CustomerMapping xmlns:i="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">[/URL]
  <CustMap NetworkId="AN01000018306-T" CustID="Testing" /> 
  <CustMap NetworkId="AN01000018304-T" CustID="Nobody" /> 
  </CustomerMapping>

Over time, there could be more CustMaps, but never very many.

I have a sql table that contains CustID elsewhere, and I will need to use that to come get the NetworkID.

How do I query this? I found t-sql to bring it into a table, so I don't mind doing that, but I'm having a hard time getting the attributes out of there using a query. I haven't done much XML in SQL, I'm just vaguely aware that this is supposed to be "easy".

 

[gmmastros]

What version of SQL server are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jenlion]

2005 sp3

 

[jenlion]

I have a solution, but it's not pretty. I would really love to be able to do this in a select query rather than resorting to something procedural. But, if nothing else, I did find a method that will work.

CREATE TABLE XmlImportTest
(   xmlFileName VARCHAR(300),
    xml_data xml
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT  @xmlFileName = 'c:\dir\filename.xml'
EXEC('INSERT INTO XmlImportTest(xmlFileName, xml_data) 
SELECT ''' + @xmlFileName + ''', xmlData 
FROM( SELECT  *  FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

create table #t (row int, NetworkID varchar(40), CustID varchar(30))
declare @i int
set @i =1
declare @NetworkId varchar(40)
declare @CustID varchar(30)
declare @sql varchar(5000)
set @NetworkId = ''
while @NetworkId is not null begin
	set @sql = 'insert into #t select ' + convert(varchar(5),@i) + ', 
                xml_data.value(''(/CustomerMapping/CustMap/@NetworkId)[' + convert(varchar(5), @i)+']'', ''varchar(40)''), 
				xml_data.value(''(/CustomerMapping/CustMap/@CustID)[' + convert(varchar(5), @i)+']'', ''varchar(20)'')
		   from xmlimporttest'
    
	exec(@sql)
	select @NetworkID = NetworkID from #t where row = @i
    set @i = @i + 1
end
delete from #t where NetworkID is null
select * from #t

That at least gets me the data I need in a way that I know how to deal with it. I only post this for future Googlers searching for this that wind up here and need SOMETHING. (Future Googlers note that this is not production-worthy code, it's just a quick example).

But, it would be nicer to be able to simply query the xml directly. Surely there's a way to do that... Surely, somebody knows what it is. I can't believe how difficult it is to find this information, I must be using all the wrong words.

 

[Jamfool]

It is friday ;p slow and weary minds. roll on the weekend

--Out Xml
DECLARE @xml XML 
SELECT  @xml = 
'<?xml version="1.0" encoding="UTF-8" ?> 
<CustomerMapping xmlns:i="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">[/URL]
<CustMap NetworkId="AN01000018306-T" CustID="Testing" /> 
<CustMap NetworkId="AN01000018304-T" CustID="Nobody" /> 
</CustomerMapping>'

--From Doc
SELECT  
x.y.query('.') [ShowXml],
x.y.query('data(@CustID)').value('.', 'varchar(15)') [CustID],
x.y.query('data(@NetworkId)').value('.', 'varchar(15)') [NetworkId]
FROM    @xml.nodes('./CustomerMapping/CustMap') AS x ( y ) ; 

--From Table
DECLARE @SomeTable TABLE(SomeXmlColumn xml)
INSERT INTO @SomeTable VALUES (@xml)
SELECT 
x.y.query('.') [ShowXml],
x.y.query('data(@CustID)').value('.', 'varchar(15)') [CustID],
x.y.query('data(@NetworkId)').value('.', 'varchar(15)') [NetworkId]
FROM  @SomeTable
CROSS APPLY SomeXmlColumn.nodes('./CustomerMapping/CustMap') AS x(y)